/*
 * ai
 * deepseek
 * */

const express = require("express");
const router = express.Router();

const OpenAI = require("openai");
// Please install OpenAI SDK first: `npm install openai`
const { db } = require("../../db");

//deepseek
const openai = new OpenAI({
  baseURL: "https://api.deepseek.com",
  apiKey: "sk-4bc1aa49d2634f1688deccf54ca351ab",
});

//deepseek
router.get("/", async (req, res) => {
  const { quest } = req.query;

  res.setHeader("Content-Type", "text/event-stream; charset=utf-8");
  res.setHeader("Cache-Control", "no-cache");
  res.setHeader("Connection", "keep-alive");
  res.setHeader("Access-Control-Allow-Origin", "*");

  try {
    let obj = {
      messages: [{ role: "system", content: quest }],
      model: "deepseek-chat",
      stream: true,
      temperature: 0.7,
    };
    const stream = await openai.chat.completions.create(obj);

    res.flushHeaders();

    for await (const chunk of stream) {
      const content = chunk.choices[0]?.delta?.content || "";
      if (content) {
        const buffer = Buffer.from(content, "utf-8");
        res.write(buffer);
        res.flush();
      }
    }

    res.write(Buffer.from("\n[DONE]", "utf-8"));
  } catch (error) {
    console.error("Stream error:", error);
    res.write(Buffer.from(error.message, "utf-8"));
  } finally {
    res.end();
  }
});

let json = {
  data: {
    tables: [
      {
        TABLE_NAME: "articles",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "categories",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "clwy_posts",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "data_source_configs",
        TABLE_COMMENT: "数据源配置表",
      },
      {
        TABLE_NAME: "likes",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "logs",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "module_params",
        TABLE_COMMENT: "模块表",
      },
      {
        TABLE_NAME: "orders",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "sequelizemeta",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "settings",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "sql_templates",
        TABLE_COMMENT: "SQL模板表",
      },
      {
        TABLE_NAME: "suppliers",
        TABLE_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        TABLE_COMMENT: "用户表",
      },
    ],
    columns: [
      {
        TABLE_NAME: "articles",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "articles",
        COLUMN_NAME: "title",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "articles",
        COLUMN_NAME: "content",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "articles",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "articles",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "userId",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "originalname",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "filename",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "mimetype",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "size",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "path",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "fullpath",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "url",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "type",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "tag",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "description",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "attachments",
        COLUMN_NAME: "imageType",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "categories",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "categories",
        COLUMN_NAME: "name",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "categories",
        COLUMN_NAME: "rank",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "categories",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "categories",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "courseId",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "title",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "content",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "video",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "rank",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "chapters",
        COLUMN_NAME: "free",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "clwy_posts",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "clwy_posts",
        COLUMN_NAME: "name",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "clwy_posts",
        COLUMN_NAME: "category_id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "categoryId",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "userId",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "name",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "image",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "recommended",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "introductory",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "content",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "likesCount",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "chaptersCount",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "courses",
        COLUMN_NAME: "free",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "主键ID",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "ds_name",
        COLUMN_COMMENT: "数据源名称",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "ds_code",
        COLUMN_COMMENT: "数据源编码",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "ds_type",
        COLUMN_COMMENT: "数据源类型：MYSQL/ORACLE/POSTGRESQL",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "jdbc_url",
        COLUMN_COMMENT: "JDBC连接URL",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "username",
        COLUMN_COMMENT: "用户名",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "password",
        COLUMN_COMMENT: "密码",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "status",
        COLUMN_COMMENT: "状态：0-禁用，1-启用",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "created_by",
        COLUMN_COMMENT: "创建人",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "created_time",
        COLUMN_COMMENT: "创建时间",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "updated_by",
        COLUMN_COMMENT: "更新人",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "updated_time",
        COLUMN_COMMENT: "更新时间",
      },
      {
        TABLE_NAME: "data_source_configs",
        COLUMN_NAME: "is_deleted",
        COLUMN_COMMENT: "是否删除1代表是",
      },
      {
        TABLE_NAME: "likes",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "likes",
        COLUMN_NAME: "courseId",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "likes",
        COLUMN_NAME: "userId",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "likes",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "likes",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "likes",
        COLUMN_NAME: "attachmentId",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "logs",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "logs",
        COLUMN_NAME: "level",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "logs",
        COLUMN_NAME: "message",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "logs",
        COLUMN_NAME: "meta",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "logs",
        COLUMN_NAME: "timestamp",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        COLUMN_NAME: "name",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        COLUMN_NAME: "durationMonths",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        COLUMN_NAME: "price",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        COLUMN_NAME: "rank",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        COLUMN_NAME: "description",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "memberships",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "主键ID",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "module_name",
        COLUMN_COMMENT: "模块名称",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "description",
        COLUMN_COMMENT: "描述",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "data_source_id",
        COLUMN_COMMENT: "数据源ID",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "created_by",
        COLUMN_COMMENT: "创建人",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "created_time",
        COLUMN_COMMENT: "创建时间",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "updated_by",
        COLUMN_COMMENT: "更新人",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "updated_time",
        COLUMN_COMMENT: "更新时间",
      },
      {
        TABLE_NAME: "module_params",
        COLUMN_NAME: "is_deleted",
        COLUMN_COMMENT: "是否删除1是",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "outTradeNo",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "tradeNo",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "userId",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "subject",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "totalAmount",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "paymentMethod",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "status",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "paidAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "orders",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "order_id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "product_name",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "quantity",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "unit_price",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "total_amount",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "discount",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "actual_amount",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "order_date",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "customer_region",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sales_records",
        COLUMN_NAME: "profit_margin",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sequelizemeta",
        COLUMN_NAME: "name",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "settings",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "settings",
        COLUMN_NAME: "name",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "settings",
        COLUMN_NAME: "icp",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "settings",
        COLUMN_NAME: "copyright",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "settings",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "settings",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "主键ID",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "template_name",
        COLUMN_COMMENT: "模板名称",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "template_module_id",
        COLUMN_COMMENT: "所属模块ID",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "template_content",
        COLUMN_COMMENT: "SQL模板内容",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "description",
        COLUMN_COMMENT: "模板描述",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "created_by",
        COLUMN_COMMENT: "创建人",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "created_time",
        COLUMN_COMMENT: "创建时间",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "updated_by",
        COLUMN_COMMENT: "更新人",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "updated_time",
        COLUMN_COMMENT: "更新时间",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "interface_name",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "is_deleted",
        COLUMN_COMMENT: "是否删除：1代表是",
      },
      {
        TABLE_NAME: "sql_templates",
        COLUMN_NAME: "request_param",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "suppliers",
        COLUMN_NAME: "suppliername",
        COLUMN_COMMENT: "名称",
      },
      {
        TABLE_NAME: "suppliers",
        COLUMN_NAME: "supplierid",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "suppliers",
        COLUMN_NAME: "totalcost",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "suppliers",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "id",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "email",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "username",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "password",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "nickname",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "sex",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "company",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "introduce",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "role",
        COLUMN_COMMENT: "100管理员，1会员，0普通",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "createdAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "updatedAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "avatar",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "membershipExpiredAt",
        COLUMN_COMMENT: "",
      },
      {
        TABLE_NAME: "users",
        COLUMN_NAME: "openid",
        COLUMN_COMMENT: "",
      },
    ],
  },
};
router.get("/sql", async (req, res) => {
  const { quest } = req.query;
  res.setHeader("Content-Type", "text/event-stream; charset=utf-8");
  res.setHeader("Cache-Control", "no-cache");
  res.setHeader("Connection", "keep-alive");
  res.setHeader("Access-Control-Allow-Origin", "*");

  let prompt = `  
    I hope you can act as an expert in databases.
     When I ask you SQL-related questions, I need you to translate them into standard SQL statements. 
     The entire conversation and instructions should be provided in Chinese.
      If my descriptions are not accurate enough, please provide appropriate feedback.
    下面是数据库表和列的结构，请根据我的描述生成SQL语句：
    ${JSON.stringify(json)}
    我的描述是：${quest}
  `;

  try {
    let obj = {
      messages: [{ role: "system", content: prompt }],
      model: "deepseek-chat",
      stream: true,
      temperature: 0.7,
    };
    const stream = await openai.chat.completions.create(obj);

    res.flushHeaders();

    for await (const chunk of stream) {
      const content = chunk.choices[0]?.delta?.content || "";
      if (content) {
        const buffer = Buffer.from(content, "utf-8");
        res.write(buffer);
        res.flush();
      }
    }

    res.write(Buffer.from("\n[DONE]", "utf-8"));
  } catch (error) {
    console.error("Stream error:", error);
    res.write(Buffer.from(error.message, "utf-8"));
  } finally {
    res.end();
  }
});

module.exports = router;
